
Clear-Host

# Get latest DB scripts and SSIS packages from SVN
function GetHeadFromSVN
{
    param($svnpath, $svnurl, $destinationfolder)

    Set-Location $svnpath
    svn checkout $svnurl $destinationfolder
}

# Use Red Gate SQL Compare to deploy schema changes to each DB
function SyncDB
{
    param($sqlcomparelocation, $argfile)

    Set-Location $sqlcomparelocation

    Invoke-Expression ".\sqlcompare /ArgFile:$argfile"
}

# Deploy SSIS packages
function DeployPackages 
{
	param($filepath, $ssisfolder, $server, $dtutilpath)
	
    $dtutil = $dtutilpath
	Set-Location $filepath

    # Only deploy packages that have been changed in the last hour
	$files=get-childitem *.dtsx | where {$_.LastWriteTime -gt (Get-Date).AddHours(-1)}
    
    if ($files -ne $null) {
    	foreach ($file in $files) {
    		$basename=$file.BaseName
    		$deletearguments = " /delete /SQL $ssisfolder/$basename /SourceServer $server"
    		$deployarguments = " /file $file /copy SQL``;$ssisfolder/$basename /destserver $server"
    		
    		Set-Location $dtutil
    		
    		Invoke-Expression "dtutil $deletearguments"
    		Invoke-Expression "dtutil $deployarguments"
    	}	
    }
}

<#
Usage:

GetHeadFromSVN "C:\Program Files\SlikSvn\bin" http://<svnserver>/repos/bi/Database "C:\NightlyBuilds\BI\SVN\Database"
GetHeadFromSVN "C:\Program Files\SlikSvn\bin" http://<svnserver>/repos/bi/ETL/branches/Phase2 "C:\NightlyBuilds\BI\SVN\ETL"
    
SyncDB "C:\Program Files\Red Gate\SQL Compare 9" "C:\NightlyBuilds\BI\Configuration\StagingCompare.xml"
    
DeployPackages "C:\NightlyBuilds\BI\SVN\ETL\SourceToStage" "SourceToStage" "<target server>" "C:\Program Files\Microsoft SQL Server\100\DTS\Binn\"
DeployPackages "C:\NightlyBuilds\BI\SVN\ETL\StageToWarehouse" "StageToWarehouse" "<target server>" "C:\Program Files\Microsoft SQL Server\100\DTS\Binn\"
#>
